package com.bdqn.dao.Impl;

import com.bdqn.dao.BaseDao;
import com.bdqn.dao.DepartmentDao;
import com.bdqn.entity.CheckWork;
import com.bdqn.entity.DepInfo;
import com.bdqn.entity.Department;
import com.bdqn.entity.Employee;
import com.bdqn.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class DepartmentDaoImpl extends BaseDao implements DepartmentDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public void insert(List<Department> depts) {
        String sql = "INSERT INTO department(depId,depname) VALUES(?,?)";
        List<Object[]> paramsList = new ArrayList<>();
        for (Department dept : depts) {
            Object[] params = {dept.getDepId(),dept.getDepname()};
            paramsList.add(params);
        }
        this.executeBatch(sql, paramsList);
    }

    @Override
    public List<DepInfo> findEmpCount() {
        String sql = "select count(e.empId) as count,d.depname from department d inner join employee e on e.depId = d.depId GROUP BY depname";
        return template.query(sql,new BeanPropertyRowMapper<DepInfo>(DepInfo.class));
    }

    @Override
    public List<CheckWork> findByPage(int depId, String like_name, int offset, int pageSize) {
        StringBuilder sql = new StringBuilder(" select e.depId as depId,d.depname as depname ,e.empId as empId,name from employee e ,department d " +
                "where e.depId=d.depId and 1=1 ");
        List params = new ArrayList();
        if(like_name!=null && like_name.length()>0 && !"null".equals(like_name)){
            sql.append(" and e.empId like? ");
            params.add("%"+like_name+"%");
        }
        if(depId > 0){
            sql.append(" and d.depId=? ");
            params.add(depId);
        }
        sql.append(" limit ?,?");
        params.add(offset);
        params.add(pageSize);
        return template.query(sql.toString(),new BeanPropertyRowMapper<CheckWork>(CheckWork.class),params.toArray());
    }

    @Override
    public int findCount(int depId,String like_name) {
        /*String sql = "select count(*) from employee";
        return template.queryForObject(sql,Integer.class);*/
        StringBuilder sql = new StringBuilder("select count(*) from employee e,department d where e.depId=d.depId ");
        List params = new ArrayList();
        if(depId >0 ){
            sql.append(" and d.depId=? ");
            params.add(depId);
        }
        if(like_name!=null && like_name.length()>0 && !"null".equals(like_name)){
            sql.append(" and e.empId like ?");
            params.add("%"+like_name+"%");
        }
        return template.queryForObject(sql.toString(),Integer.class,params.toArray());
    }

    @Override
    public List<Department> findAllDep() {
        String sql = "select * from department";
        return template.query(sql,new BeanPropertyRowMapper<Department>(Department.class));
    }
}
